<?php
/**
 * OS CRM SYSTEM - An open source application development framework for PHP 4.3.2 or newer
 *
 * CLASS - JOURNALS - OS Admin Mass Upload
 * 
 * @name      OS Admin Mass Upload
 * @package		OS CRM
 * @category  Class
 * @author		CRM SYSTEM Dev Team
 * @copyright	Copyright (c) 2010 - 2012, CRM SYSTEM
 * @since     Version 1.0
 * @filesource
 * @access    public 
 */

// ------------------------------------------------------------------------

class os_admin_massupload extends Controller {
  
  /*common class varibale*/
  var $c 	= 'os_admin_massupload';//current controler
	var $p_c = 'journals';//parent controller
    
  /*message strings*/
  var $valid_success 	= '';
  var $valid_errors 	= '';
  var $valid_notice 	= '';
  
  /**
   * os_admin_massupload  - constructor of the class
   * 
   * @access public
   * @param  NA
   * @return void
   */
	function os_admin_massupload()
	{
		parent::Controller();	
    
    if(!__admin_loggedin()){
      redirect('login');
    }
    
    $this->lang->load('common', ADMIN_LANGUAGE);
    $this->load->model('DB_Model', 'db_model');
	}
	
  /** 
	 * Index
	 *
	 * @desc NA
	 * @access public/private
	 * @param  NA	 
	 * @return void/mixed
	 */
	function index()
	{
    redirect($this->p_c);
	}
  
  function upload_data(){
    /*truncate all
     * 
      TRUNCATE `os_articles`;
      TRUNCATE `os_category`;
      TRUNCATE `os_db_log`;
      TRUNCATE `os_editor`;
      TRUNCATE `os_journals`;
      TRUNCATE `os_journals_cat`;
      TRUNCATE `os_journals_editor`;
      TRUNCATE `os_journals_pub`;
      TRUNCATE `os_media`;
      TRUNCATE `os_pub`;
      TRUNCATE `os_render_page_log`;
      TRUNCATE `os_subjects`;
      TRUNCATE `os_volume`;
     * 
     */
    $data = array();
		$data['cont_title'] = 'Mass Uploader';
    		
    $data['frm_name']   = 'frmjournalsrchlist';
    $data['frm_action'] = site_url( $this->c );
    
		//set crontroller info
		$data['c'] 		= $this->c;
		$data['p_c'] 	= $this->p_c;
    $data['cur_c'] 	= $this->p_c.'/'.$this->c;
    __admin_display('mass_uploader', $data);
    /*$this->mass_upload();
    $this->mass_upload_volume();
    $this->mass_upload_articles();*/
  }
  
  function mass_upload(){
    error_reporting(0);
    __debug('<hr/> Mass upload journals');
    require_once (APPPATH . 'libraries/excel_reader2.php');
    $excel_file = ROOTBASEPATH . "images/media/data_uploads/journal_info.xls";
    //$excel_file = "C:\\wamp\\www\\projects\\IBH\\trunk\\www\\images\\media\\data_uploads\\journal_info.xls";
    echo '';
    if(is_file($excel_file)){
      echo 'File exists '.$excel_file;
    }  else {
      die ('File does not exists: '.$excel_file);
    }
    $data = new Spreadsheet_Excel_Reader( $excel_file );
    $sheet_1 = (isset($data->sheets[0])) ? $data->sheets[0]:array();
    if(count($sheet_1) > 0 && $sheet_1['numRows'] > 1){
      echo '<br/> Num rows: '.$sheet_1['numRows'];
      $cells = $sheet_1['cells'];
      
      /*[1] => Journal Title-
        [2] => abstract-
        [3] => price-
        [4] => status-
        [5] => issues
        [6] => isssn-
        [7] => e-issn-
        [8] => long descp-
        [9] => editor in chief-
        [10] => co-editors-
        [11] => Editorial Board-
        [12] => category
        [13] => access type-
        [14] => subscription-
        [15] => copyright info-
        [16] => publisher
        [17] => cover img-
        [18] => table of contents-*/
      
      for($i = 2; $i <= $sheet_1['numRows']; $i++){
        
        $issn_no = filter_data($cells[$i][6]);
        
        if(!empty ($issn_no)){
        
          $qry = "SELECT os_journals_id, os_journals_title 
              FROM ".TBL_JOURNALS."
              WHERE os_journals_issn = '".$issn_no."'
              ";		
          __debug('<br>Jounral ISSN: '.$issn_no);
          $return_arr = run_cust_qry($qry, TRUE);
          if($return_arr['num_rows'] == 0){
            $data = array();      
            $status = 1;//$cells[$i][4];
            $status = (!empty($status)) ? $status:0;     

            $data['os_journals_status'] = '1';
            $data['os_journals_title'] = filter_data($cells[$i][1]);
            $data['os_journals_disclaimer'] = filter_data($cells[$i][15]);
            $data['os_journals_copyright'] = filter_data($cells[$i][15]);
            $data['os_journals_issn'] = filter_data($cells[$i][6]);
            $data['os_journals_e_issn'] = filter_data($cells[$i][7]);

            $data['os_journals_editorial'] = filter_data($cells[$i][11]);
            $data['os_journals_co_editors'] = filter_data($cells[$i][10]);
            $data['os_journals_editors_in_chief'] = filter_data($cells[$i][9]);
            $data['os_journals_toc'] = filter_data($cells[$i][18]);

            $access = filter_data($cells[$i][13]);
            /*Type of asccess journals, 1 = Paid Access, 2 = Open Access 	os_journals_content_type*/
            $data['os_journals_access'] = (strtolower($access)== 'paid')? 1:2;        
            $data['os_journals_price'] = filter_data($cells[$i][3]);
            $data['os_journals_abstract'] = filter_data($cells[$i][2]);
            $data['os_journals_descp'] = filter_data($cells[$i][8]);
            $data['os_journals_subscription'] = filter_data($cells[$i][14]);
            $data['os_journals_issue'] = filter_data($cells[$i][5]);

            $data['os_journals_cover_img'] = filter_data($cells[$i][17]);
            $data['os_journals_content_type'] =  'Journal';
            $data['os_journals_created'] = date(DB_DATE_FORMAT);

            $this->db->insert(TBL_JOURNALS, $data); 
            $last_id = $this->db->insert_id();
            __debug("<br>Jounral : ".$data['os_journals_title']." added , ID: ".$last_id);

            /*add a folder for journal cover imgs*/        
            $journal_files = ROOTBASEPATH . "/images/media/data_uploads/journals/";
            $dir = ROOTBASEPATH . "/images/media/journals/";
            if(!is_dir($dir.$last_id)){
              mkdir($dir.$last_id);
              __debug( '<br>>> Dir created '.$dir.$last_id );          
            }

            __debug("<br/> File path: ".$journal_files.$data['os_journals_cover_img']);
            $dest = $dir.$last_id.'/'.$data['os_journals_cover_img'];
            if(is_file($journal_files.$data['os_journals_cover_img'])){
              copy($journal_files.$data['os_journals_cover_img'], $dest);
              __debug( '<br>>>File moved '.$dest);
            }


            /*add editors info*/
            __debug( '<br>>Add new Editors');
            $editors = array();
            $editors = explode(';', strip_tags($cells[$i][11]));
            foreach ($editors as $value) {

              $qry = "SELECT os_editor_id 
                  FROM ".TBL_EDITOR."
                  WHERE os_editor_name ".filter_data($value,'search_like')."
                  ";			
              //echo $qry;exit ();
              $edit_data      = run_cust_qry($qry,TRUE);
              if($edit_data['num_rows'] == 0){

                $data = array();      
                $data['os_editor_name'] = filter_data($value);
                $data['os_editor_status'] = 1;
                $data['os_editor_created'] = date(DB_DATE_FORMAT);          
                $this->db->insert(TBL_EDITOR, $data); 
                $last_edit_id = $this->db->insert_id();
                __debug( '<br>>>Editor '.$value.", ID ".$last_edit_id);

                $data = array();      
                $data['os_journals_id'] = $last_id;
                $data['os_editor_id']   = $last_edit_id;
                $this->db->insert(TBL_JOR_MAP_EDITOR, $data);                 
                }else{
                $os_editor_id = $edit_data['list']['os_editor_id'];
                $data = array();      
                $data['os_journals_id'] = $last_id;
                $data['os_editor_id'] = $os_editor_id;
                $this->db->where('os_editor_id', $os_editor_id);
                $this->db->update(TBL_JOR_MAP_EDITOR, $data);    

              }
            }

            /*check and insert catgeory data*/
            $category = filter_data($cells[$i][12]);
             __debug( '<br>>$category -> '.$category);  
            if (!empty($category)) {
              __debug('<br>>Add new Category');
              $qry = "SELECT os_category_id 
                  FROM " . TBL_CATEGORY . "
                  WHERE os_category_name like '" . $category . "'
                  ";
              //echo $qry;exit ();
              $cat_data = run_cust_qry($qry, TRUE);
              if ($cat_data['num_rows'] == 0) {
                $data = array();
                $data['os_category_name'] = filter_data($category);
                $data['os_category_status'] = '1';
                $data['os_category_created'] = date(DB_DATE_FORMAT);
                $this->db->insert(TBL_CATEGORY, $data);
                $os_category_id = $this->db->insert_id();
                __debug('<br> new Category inserted: ' . $category . ", ID " . $os_category_id);
              } else {
                $os_category_id = $cat_data['list']['os_category_id'];                
              }

              /*update category info*/
              $datai = array();
              $datai['os_journals_id'] = $last_id;
              $datai['os_category_id'] = $os_category_id;
              $this->db->insert(TBL_JOR_MAP_CAT, $datai);
              __debug('<br>>>Category updated -> ' . $category . ", ID " . $os_category_id);
            }
            
            $publisher = filter_data($cells[$i][16]);
            __debug( '<br>>$publisher -> '.$publisher);  
            /*check and insert publishers data*/
            if(!empty($publisher)){
              __debug( '<br>>Add new Publishers');

              $qry = "SELECT os_pub_id
                  FROM ".TBL_PUB."
                  WHERE os_pub_name like '".$publisher."'
                  ";			
              //echo $qry;exit ();
              $pub_data      = run_cust_qry($qry,TRUE);
              if($pub_data['num_rows'] == 0){
                $data = array();      
                $data['os_pub_name'] = filter_data($publisher);
                $data['os_pub_status'] = '1';
                $data['os_pub_created'] = date(DB_DATE_FORMAT);          
                $this->db->insert(TBL_PUB, $data); 
                $os_pub_id = $this->db->insert_id();
                __debug( '<br>>>Publishers '.$publisher.", ID ".$os_pub_id);
              }else{
                $os_pub_id = $pub_data['list']['os_pub_id'];
              }    

              /*update publishers info*/
              $datai = array();
              $datai['os_journals_id'] = $last_id;
              $datai['os_pub_id'] = $os_pub_id;
              $this->db->insert(TBL_JOR_MAP_PUB, $datai);
              
            }else{
              __debug('<br>***Publishers already exists : '.$issn_no);
            }
          }
        
        }
        
      }
    }
    
  }
    
  
  
  function mass_upload_volume(){
    error_reporting(0);
    __debug('<hr/> Mass upload Volume');
    require_once (APPPATH . 'libraries/excel_reader2.php');
    $excel_file = ROOTBASEPATH . "images/media/data_uploads/volume_issue_info.xls";
    //$excel_file = "C:\\wamp\\www\\projects\\IBH\\trunk\\www\\images\\media\\data_uploads\\volume_issue_info.xls";
    if(is_file($excel_file)){
      echo 'File exists '.$excel_file;
    }  else {
      die('File does not exists: '.$excel_file);
    }
    $data = new Spreadsheet_Excel_Reader( $excel_file );
    $sheet_1 = (isset($data->sheets[0])) ? $data->sheets[0]:array();
    if(count($sheet_1) > 0 && $sheet_1['numRows'] > 1){
      __debug('<br/> Num rows: '.$sheet_1['numRows']);
      $cells = $sheet_1['cells'];
      
      /*[1] => isssn
            [2] => volume no
            [3] => volume year
            [4] => issue no
            [5] => price
            [6] => rel date
            [7] => expected rel date
            [8] => pages
            [9] => abstract
            [10] => contents*/
      
      for($i = 2; $i <= $sheet_1['numRows']; $i++){
        
        $data = array();                      
        $issn_no = filter_data($cells[$i][1]);
        $qry = "SELECT os_journals_id, os_journals_title 
            FROM ".TBL_JOURNALS."
            WHERE os_journals_issn = '".$issn_no."'
            ";		
        __debug('<br>Jounral ISSN: '.$issn_no);
        $return_arr = run_cust_qry($qry, TRUE);
        if($return_arr['num_rows'] > 0){
          $journal_id = $return_arr['list']['os_journals_id'];       
          if(!empty ($journal_id)){
            $qry = "SELECT *
                    FROM ".TBL_VOLUME." 
                    WHERE os_journals_id = '".$journal_id."'
                    AND os_volume_no  = '".filter_data($cells[$i][2])."'
                    AND os_volume_issue_no  = '".substr(filter_data($cells[$i][4]), 0,1)."'
                    ";
            //__debug($qry);exit;
            $return_arr = run_cust_qry($qry, TRUE);
            if($return_arr['num_rows'] == 0){
              $data['os_journals_id'] = $journal_id;
              $data['os_volume_year'] = filter_data($cells[$i][3]);
              $data['os_volume_no'] = filter_data($cells[$i][2]);
              $data['os_volume_issue_no'] = substr(filter_data($cells[$i][4]), 0,1);
              $data['os_volume_issue_dt_range'] = filter_data($cells[$i][4]);
              $data['os_volume_abtract'] = filter_data($cells[$i][9]);
              $data['os_volume_content'] = filter_data($cells[$i][10]);
              $data['os_volume_price'] = filter_data($cells[$i][5]);
              $data['os_volume_pages'] = filter_data($cells[$i][8]);

              $data['os_volume_created'] = date(DB_DATE_FORMAT);
              $data['os_volume_status'] = '1';
              $this->db->insert(TBL_VOLUME, $data);           

              __debug('<br>New volume added : '.$data['os_volume_no']);
            }else{
              __debug("*** Volume already exists : ".filter_data($cells[$i][2]));
            }        
          }
        }
      }
    }
    
  }
  
  function mass_upload_articles(){
    __debug('<hr/> Mass upload Articles');
    error_reporting(0);
    require_once (APPPATH . 'libraries/excel_reader2.php');
    $excel_file = ROOTBASEPATH . "images/media/data_uploads/article_info.xls";
    //$excel_file = "C:\\wamp\\www\\projects\\IBH\\trunk\\www\\images\\media\\data_uploads\\article_info.xls";
    if(is_file($excel_file)){
      echo 'File exists '.$excel_file;
    }else{
      die ("File does not exists: ".$excel_file);
    }
    $data = new Spreadsheet_Excel_Reader( $excel_file );
    $sheet_1 = (isset($data->sheets[0])) ? $data->sheets[0]:array();
    if(count($sheet_1) > 0 && $sheet_1['numRows'] > 1){
      __debug('<br/> Num rows: '.$sheet_1['numRows']);
      $cells = $sheet_1['cells'];
      
      /*[1] => isssn
            [2] => volume no
            [3] => volume year
            [4] => issue no
            [5] => article title
            [6] => price
            [7] => abstract
            [8] => content
            [9] => rel date
            [10] => pdf file of artcile
            [11] => paid/free
            [12] => author names
            [13] => various media files
            [14] => pages
       */
      echo '<br> Sheet num rows: '.$sheet_1['numRows'];
      $dir_no = 1;
      for($i = 2; $i <= $sheet_1['numRows']; $i++){
        
        $data = array();                      
        $issn_no = filter_data($cells[$i][1]);
        $os_volume_year = filter_data($cells[$i][3]);
        $os_volume_no = filter_data($cells[$i][2]);
        $os_volume_issue_no = substr(filter_data($cells[$i][4]), 0,1);
        $os_volume_issue_dt_range = filter_data($cells[$i][4]);
        $qry = "SELECT j.os_journals_id, j.os_journals_title, v.os_volume_id
            FROM ".TBL_JOURNALS." j
            JOIN ".TBL_VOLUME." v             
            ON j.os_journals_id = v.os_journals_id  
            WHERE j.os_journals_issn = '".$issn_no."'
            AND v.os_volume_no  = '".$os_volume_no."'
            AND v.os_volume_issue_no  = '".$os_volume_issue_no."'
            ";
        //__debug($qry);exit;
        $return_arr = run_cust_qry($qry, TRUE);
        if($return_arr['num_rows'] > 0){
          __debug('<br> Journal and Volume '.$issn_no);
          __debug('<br> Volume Exists'.$os_volume_no.", Date: ".$os_volume_issue_dt_range);
          
          $journal_id = $return_arr['list']['os_journals_id'];
          $os_volume_id = $return_arr['list']['os_volume_id'];
          
          if(!empty($journal_id) && !empty ($os_volume_id)){
          
            $qry = "SELECT * 
                    FROM ".TBL_ARTICLES."                                
                    WHERE 
                    os_journals_id = '".$journal_id."'
                    AND os_volume_id = '".$os_volume_id."'
                    AND `os_articles_title` LIKE CONVERT(_utf8 '".filter_data($cells[$i][5],'like')."' USING latin1) COLLATE latin1_swedish_ci
                    ";
            //__debug($qry);exit;
            $return_arr = run_cust_qry($qry, TRUE);
            if($return_arr['num_rows'] == 0){

              $data['os_journals_id'] = $journal_id;
              $data['os_volume_id'] = $os_volume_id;
              $data['os_articles_title'] = filter_data($cells[$i][5]);
              $data['os_articles_abstract'] = filter_data($cells[$i][7]);
              $data['os_articles_content'] = filter_data($cells[$i][8]);
              $data['os_articles_pages'] = filter_data($cells[$i][14]);
              $data['os_articles_status'] = '1';
              $data['os_articles_created'] = date(DB_DATE_FORMAT);
              $data['os_articles_rel_dt'] = filter_data($cells[$i][9]);          
              $data['os_articles_acess_type'] = (strtolower(filter_data($cells[$i][11])) == 'paid') ? '1':'0';
              $data['os_articles_author'] = filter_data($cells[$i][12]);
              $data['os_articles_pdf'] = filter_data($cells[$i][10]);
              $data['os_articles_media'] = filter_data($cells[$i][13]);
              $data['os_articles_price'] = filter_data($cells[$i][6]);

              $this->db->insert(TBL_ARTICLES, $data);    
              $dir_no = $this->db->insert_id();
              __debug('<br/> -> insert article - > '.filter_data($cells[$i][5]));

              /*add a folder for articles PDF*/        
              $journal_files = ROOTBASEPATH . "/images/media/data_uploads/articles/".$issn_no."/";
              $dir = ROOTBASEPATH . "/images/media/articles/";
              if(!is_dir($dir.$dir_no)){
                mkdir($dir.$dir_no);          
                __debug ('<br>New articles dir created > '.$dir.$dir_no);
                __debug ('<br>Articles PDF files > '.$data['os_articles_pdf']);
                __debug ('<br>Articles Media files > '.$data['os_articles_media']);
              }

              __debug("<br/> File path: ".$journal_files.$data['os_articles_pdf']);
              $dest = $dir.$dir_no.'/'.$data['os_articles_pdf'];
              if(is_file($journal_files.$data['os_articles_pdf'])){
                copy($journal_files.$data['os_articles_pdf'], $dest);
                __debug( '<br>>>File moved '.$dest);
              }
            }  else {
              __debug('<br/> -> ***** article exists - > '.filter_data($cells[$i][5]));
            }      
          
          }
        }
      }
    }
    
  }
  
  function mass_upload_articles_price(){
    __debug('<hr/> Mass upload Articles');
    error_reporting(0);
    require_once (APPPATH . 'libraries/excel_reader2.php');
    $excel_file = ROOTBASEPATH . "images/media/data_uploads/article_info.xls";
    //$excel_file = "C:\\wamp\\www\\projects\\IBH\\trunk\\www\\images\\media\\data_uploads\\article_info.xls";
    if(is_file($excel_file)){
      echo 'File exists '.$excel_file;
    }else{
      die ("File does not exists: ".$excel_file);
    }
    $data = new Spreadsheet_Excel_Reader( $excel_file );
    $sheet_1 = (isset($data->sheets[0])) ? $data->sheets[0]:array();
    if(count($sheet_1) > 0 && $sheet_1['numRows'] > 1){
      __debug('<br/> Num rows: '.$sheet_1['numRows']);
      $cells = $sheet_1['cells'];
      
      /*[1] => isssn
            [2] => volume no
            [3] => volume year
            [4] => issue no
            [5] => article title
            [6] => price
            [7] => abstract
            [8] => content
            [9] => rel date
            [10] => pdf file of artcile
            [11] => paid/free
            [12] => author names
            [13] => various media files
            [14] => pages
       */
      echo '<br> Sheet num rows: '.$sheet_1['numRows'];
      $dir_no = 1;
      for($i = 2; $i <= $sheet_1['numRows']; $i++){
        __debug('<br>price '.$cells[$i][6]);
        
        $qry = "SELECT * 
                FROM ".TBL_ARTICLES."                                
                WHERE `os_articles_title` LIKE CONVERT(_utf8 '".filter_data($cells[$i][5],'like')."' USING latin1) COLLATE latin1_swedish_ci
                ";
        __debug('<br>'.$qry);
        $return_arr = run_cust_qry($qry, TRUE);
        if($return_arr['num_rows'] > 0){
          $os_articles_id = $return_arr['list']['os_articles_id'];
          $os_articles_price = filter_data($cells[$i][6]);

          $qryup = "UPDATE ".TBL_ARTICLES."   
                  SET os_articles_price = '".$os_articles_price."'
                  WHERE os_articles_id = '".$os_articles_id."'  ";     
          __debug('<br>--->'.$qryup);
          exec_cust_qry($qryup);
        }
      }
    }
  }
  
  function upload_artiles_pdf($param) {
    
    $issn_no = "0974-9942";
    $qry = "SELECT j.os_journals_id, j.os_journals_title, v.os_volume_id
            FROM ".TBL_JOURNALS." j
            JOIN ".TBL_ARTICLES." a
            ON j.os_journals_id = a.os_journals_id  
            WHERE j.os_journals_issn = '".$issn_no."'            
            ";
    __debug($qry);exit;
    $return_arr = run_cust_qry($qry, TRUE);
    if($return_arr['num_rows'] > 0){

    }
    $dir_no = 0;
    exit;
    /*add a folder for articles PDF*/        
    $journal_files = ROOTBASEPATH . "/images/media/data_uploads/articles/";
    $dir = ROOTBASEPATH . "/images/media/articles/";
    if(!is_dir($dir.$dir_no)){
      mkdir($dir.$dir_no);          
      __debug ('<br>New articles dir created > '.$dir.$dir_no);
      __debug ('<br>Articles PDF files > '.$data['os_articles_pdf']);
      __debug ('<br>Articles Media files > '.$data['os_articles_media']);
    }

    __debug("<br/> File path: ".$journal_files.$data['os_articles_pdf']);
    $dest = $dir.$dir_no.'/'.$data['os_articles_pdf'];
    if(is_file($journal_files.$data['os_articles_pdf'])){
      copy($journal_files.$data['os_articles_pdf'], $dest);
      __debug( '<br>>>File moved '.$dest);
    }
  }
  
  function mass_update_journal_cat(){
    error_reporting(0);
    __debug('<hr/> Mass upload journals');
    $sheet_1 = $this->_get_journal_data_sheet();
    if(count($sheet_1) > 0 && $sheet_1['numRows'] > 1){
      echo '<br/> Num rows: '.$sheet_1['numRows'];
      $cells = $sheet_1['cells'];
            
      for($i = 2; $i <= $sheet_1['numRows']; $i++){
        
        $issn_no = filter_data($cells[$i][6]);
        
        if(!empty ($issn_no)){
        
          $qry = "SELECT os_journals_id, os_journals_title 
              FROM ".TBL_JOURNALS."
              WHERE os_journals_issn = '".$issn_no."'
              ";		
          __debug('<br>Jounral ISSN exists found: '.$issn_no);
          $return_arr = run_cust_qry($qry, TRUE);
          if($return_arr['num_rows'] > 0){
            $last_id = $return_arr['list']['os_journals_id'];
            __debug('<br>Jounral ID: '.$last_id);
            
            /* check and insert catgeory data */
            $category = filter_data($cells[$i][12]);
            __debug('<br>>$category to be updated -> ' . $category);
            if (!empty($category)) {
              __debug('<br>>Add new Category');
              $qry = "SELECT os_category_id 
                  FROM " . TBL_CATEGORY . "
                  WHERE os_category_name like '" . $category . "'
                  ";
              //echo $qry;exit ();
              $cat_data = run_cust_qry($qry, TRUE);
              if ($cat_data['num_rows'] == 0) {
                $data = array();
                $data['os_category_name'] = filter_data($category);
                $data['os_category_status'] = '1';
                $data['os_category_created'] = date(DB_DATE_FORMAT);
                $this->db->insert(TBL_CATEGORY, $data);
                $os_category_id = $this->db->insert_id();
                __debug('<br> new Category inserted: ' . $category . ", ID " . $os_category_id);
              } else {
                $os_category_id = $cat_data['list']['os_category_id'];                
              }

              /*update category info*/
              $del = "DELETE FROM " . TBL_JOR_MAP_CAT . " WHERE os_category_id = '".$os_category_id."' AND os_journals_id = '".$last_id."' ";
              exec_cust_qry($del);
              $datai = array();
              $datai['os_journals_id'] = $last_id;
              $datai['os_category_id'] = $os_category_id;
              $this->db->insert(TBL_JOR_MAP_CAT, $datai);
              __debug('<br>>>Category updated -> ' . $category . ", ID " . $os_category_id);
            }
          }
        
        }
        
      }
    }
    
  }
  
  function mass_update_journal_pub(){
    error_reporting(0);
    __debug('<hr/> Mass upload publishers');
    $sheet_1 = $this->_get_journal_data_sheet();
    if(count($sheet_1) > 0 && $sheet_1['numRows'] > 1){
      echo '<br/> Num rows: '.$sheet_1['numRows'];
      $cells = $sheet_1['cells'];
            
      for($i = 2; $i <= $sheet_1['numRows']; $i++){
        
        $issn_no = filter_data($cells[$i][6]);
        
        if(!empty ($issn_no)){
        
          $qry = "SELECT os_journals_id, os_journals_title 
              FROM ".TBL_JOURNALS."
              WHERE os_journals_issn = '".$issn_no."'
              ";		
          __debug('<br>Jounral ISSN exists found: '.$issn_no);
          $return_arr = run_cust_qry($qry, TRUE);
          if($return_arr['num_rows'] > 0){
            $last_id = $return_arr['list']['os_journals_id'];
            __debug('<br>Jounral ID: '.$last_id);
            
            $publisher = filter_data($cells[$i][16]);
            __debug( '<br>>$publisher -> '.$publisher);  
            /*check and insert publishers data*/
            if(!empty($publisher)){
              __debug( '<br>>Add new Publishers');

              $qry = "SELECT os_pub_id
                  FROM ".TBL_PUB."
                  WHERE os_pub_name like '".$publisher."'
                  ";			
              //echo $qry;exit ();
              $pub_data      = run_cust_qry($qry,TRUE);
              if($pub_data['num_rows'] == 0){
                $data = array();      
                $data['os_pub_name'] = filter_data($publisher);
                $data['os_pub_status'] = '1';
                $data['os_pub_created'] = date(DB_DATE_FORMAT);          
                $this->db->insert(TBL_PUB, $data); 
                $os_pub_id = $this->db->insert_id();
                __debug( '<br>>>Publishers '.$publisher.", ID ".$os_pub_id);
              }else{
                $os_pub_id = $pub_data['list']['os_pub_id'];
              }    

              /*update publishers info*/
              $del = "DELETE FROM " . TBL_JOR_MAP_PUB . " WHERE os_pub_id = '".$os_pub_id."' AND os_journals_id = '".$last_id."' ";
              exec_cust_qry($del);
              $datai = array();
              $datai['os_journals_id'] = $last_id;
              $datai['os_pub_id'] = $os_pub_id;
              $this->db->insert(TBL_JOR_MAP_PUB, $datai);
              
            }
          }
        
        }
        
      }
    }
    
  }
  
  function _get_journal_data_sheet() {
    $var = array();
    require_once (APPPATH . 'libraries/excel_reader2.php');
    $excel_file = ROOTBASEPATH . "images/media/data_uploads/journal_info.xls";
    //$excel_file = "C:\\wamp\\www\\projects\\IBH\\trunk\\www\\images\\media\\data_uploads\\journal_info.xls";
    echo '';
    if(is_file($excel_file)){
      echo 'File exists '.$excel_file;
    }  else {
      die ('File does not exists: '.$excel_file);
    }
    $data = new Spreadsheet_Excel_Reader( $excel_file );
    $var = (isset($data->sheets[0])) ? $data->sheets[0]:array();
    return $var;
  }
  
  function mass_move_toc(){
    
    /*$journals_data  = array();
		$qry = "SELECT os_journals_id, os_journals_toc 
            FROM ".TBL_JOURNALS."
            WHERE os_journals_toc != ''  
            ";			
    //__debug($qry);exit ();
		$journals_data = run_cust_qry($qry);
    if($journals_data['num_rows'] > 0){
      foreach($journals_data['list'] as $val){
        $qry = "UPDATE os_volume SET os_volume_toc = '".$val['os_journals_toc']."' WHERE os_journals_id = '".$val['os_journals_id']."'";
        exec_cust_qry($qry);
      }
      
    }*/
  }
    
  function download($key = ''){
    
    output_file($file, $name, $mime_type);
  }
  
  /** 
	 * Validation Form
	 *
	 * @descp NA
	 * @access private
	 * @param  NA	 
	 * @return array
	 */
	function _set_post_data()
	{
    $var = array();    
    if(isset($_POST)){      
      foreach($_POST as $key => $val){
        $var[$key] = $this->input->post($key); 
      }
    }    
    return $var;
  }
  
}

/* End of file login.php */
/* Location: ./system/application/modules/login/controllers/login.php */